package cn.dida.dao;

import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;

import org.apache.commons.lang3.ArrayUtils;
import org.apache.commons.lang3.StringUtils;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import cn.dida.model.Activity;
import cn.dida.model.ActivityPrize;
import cn.dida.model.ActivityProduct;
import cn.dida.model.ActivityRegion;
import cn.dida.model.Pageable;
import cn.dida.model.Product;
import cn.dida.model.RgnRegion;

/**
 * <p>活动 数据交互类</p>
 * @author lisha
 * @date 2016-11-14 10:32
 * */
@Repository
public class ActivityDao extends DaoBase {
	/**
	 * 删除活动与商品关联
	 * @param productId 商品id
	 * @return int 受影响的记录行数
	 * @date 2016-11-18 10:35
	 * */
	public int deleteActivityProduct(int productId) {
        
		//删除活动与商品关联(即已选择的商品)
        String proSql = "delete from activity_product where product_id = ?";
        return jdbcTemplate.update(proSql,productId);
	}
	/**
	 * 根据活动id获取活动详情
	 * @param activityId 活动id
	 * @return Activity 活动实体信息
	 * @date 2016-11-18 17:40
	 * */
	public Activity getActivityById(String activityId) {
        String sql = "select * from activity where id = ?";
        try {
        	Activity activity = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<Activity>(Activity.class), activityId);
            return activity;
        } catch (DataAccessException e) {
            return null;
        }
    }
	/**
	 * 根据活动id获取奖品规则
	 * @param activityId 活动id
	 * @return Activity 活动实体信息
	 * @date 2016-11-18 17:40
	 * */
	/*public ActivityPrize getActivityPrizeById(String activityId) {
        String sql = "select * from activity_prize where activity_id = ?";
        try {
        	ActivityPrize aPrize = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<ActivityPrize>(ActivityPrize.class), activityId);
            return aPrize;
        } catch (DataAccessException e) {
            return null;
        }
    }*/
	/**
	 * 删除活动
	 * @param id 活动id,记录行号
	 * @return int 受影响的记录行数
	 * @date 2016-11-18 10:35
	 * */
	public int delete(Integer companyId,int id) {
        
		 //删除活动
        String sql = "delete from activity where id = ? and company_id = ?";
        int i = jdbcTemplate.update(sql, id,companyId);
        
		//删除活动与商品关联(即已选择的商品)
        String proSql = "delete from activity_product where activity_id = ?";
        jdbcTemplate.update(proSql,id);
        
        //删除活动与奖品关联(即已添加的中奖规则)
        String prizeSql = "delete from activity_prize where activity_id = ?";
        jdbcTemplate.update(prizeSql,id);
        
        //删除活动与区域关联(即活动区域)
        String regionSql = "delete from activity_region where activity_id = ?";
        jdbcTemplate.update(regionSql,id);
        
        
        return i;
    }
	/**
	 * 修改活动
	 * @param activity 活动实体类
	 * @return int 返回受影响的记录行号
	 * @date 2016-11-09 14:29
	 * */
	public int update(Integer companyId,Activity activity) {
		
		Integer activityId = activity.getId();
        String sql = "update activity set name=:name,company_id=:companyId,act_time_start=:start,act_time_end=:end,"
        		+ " is_limit_scan_region=:regionLimit,act_base_nub=:nub,channel=:channel,"
        		+ "u_time=:utime, u_admin=:uadmin,cash_time_end=:cashTimeEnd,"
        		+ "background_image_get=:backgroundImageGet,background_image_win=:backgroundImageWin,background_image_los=:backgroundImageLos,"
        		+ "is_auto_draw=:isAutoDraw"
        		+ " where id = :id and company_id=:companyId";
        
        //渠道拼接，如果最后一个字符是逗号，则去掉
  		String endStr = activity.getChannel().substring(activity.getChannel().length()-1);
  		if(",".equals(endStr)){
  			String temp = activity.getChannel().substring(0, activity.getChannel().length()-1);
  			activity.setChannel(temp);
  		}
        
        HashMap<String, Object> paramMap = new HashMap<>();
        paramMap.put("name", activity.getName());
        paramMap.put("companyId", activity.getCompanyId());
        paramMap.put("start", activity.getActTimeStart());
        paramMap.put("end", activity.getActTimeEnd());
        paramMap.put("regionLimit", activity.getIsLimitScanRegion());
        paramMap.put("nub", activity.getActBaseNub());
        paramMap.put("channel", activity.getChannel());
        paramMap.put("utime", new Date().getTime());
        paramMap.put("uadmin", activity.getuAdmin());
        paramMap.put("id", activity.getId());
        paramMap.put("cashTimeEnd", activity.getActTimeEnd());
       // paramMap.put("introduction", activity.getIntroduction());
        paramMap.put("backgroundImageGet", activity.getBackgroundImageGet());
        paramMap.put("backgroundImageWin", activity.getBackgroundImageWin());
        paramMap.put("backgroundImageLos", activity.getBackgroundImageLos());
        paramMap.put("companyId", activity.getCompanyId());
        paramMap.put("isAutoDraw", activity.getIsAutoDraw());
        
        //修改活动与产品关联关系
        if(StringUtils.isNotBlank(activity.getSelectedPros())){
            //增加活动与产品关联信息
        	String[] pros = activity.getSelectedPros().split(",");//关联的商品
            ActivityProduct apro = new ActivityProduct();
            for(int i = 0; i < pros.length; i++){
            	apro.setActivityId(activityId);
            	apro.setProductId(Integer.parseInt(pros[i]));
            	apro.setcAdmin(activity.getuAdmin());
            	this.insertActivityProduct(apro);
            }
        }
        //删除已选择的商品
        if(StringUtils.isNotBlank(activity.getDelSelectedPros())){
        	String[] delPros = activity.getDelSelectedPros().split(",");
        	if(delPros.length > 0){
        		for(int i = 0; i < delPros.length; i++){
        			this.deleteActivityProduct(Integer.valueOf(delPros[i]));
        		}
        	}
        }
        
        //修改活动与区域关联
        if(StringUtils.isNotBlank(activity.getSelectedRgn())){
        	String[] rgns = activity.getSelectedRgn().split(",");
        	 //增加活动与区域关联
	        ActivityRegion r = new ActivityRegion();
	        for(int i = 0; i < rgns.length; i++){
	        	r.setActivityId(activityId);
	        	r.setRgnRegionId(Integer.parseInt(rgns[i]));
	        	//
	        	if(this.getActivityRgnByrgnid(activity.getId(),rgns[i]) == 0){//不存在相同的区域
	        		this.insertActivityRegion(r);
	        	}
	        }
        }
        
        return namedParameterJdbcTemplate.update(sql, paramMap);
    }
	
	private int getActivityRgnByrgnid(Integer activityId,String rgnid){
		String sql = "select * from activity_region where rgn_region_id = ? and activity_id = ?";
		List<ActivityRegion> result = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(ActivityRegion.class),rgnid,activityId);
		return result.size();
	}
	
	/**
	 * 修改活动规则
	 * @param activity 活动实体类
	 * @return int 返回受影响的记录行号
	 * @date 2016-11-24 11:37
	 * */
	public int updateStract(Activity activity) {
		String sql = "update activity set introduction=:introduction where id = :id";
		HashMap<String, Object> paramMap = new HashMap<>();
		paramMap.put("id", activity.getId());
		String str = activity.getIntroduction();
		paramMap.put("introduction", str); 
		return namedParameterJdbcTemplate.update(sql, paramMap);
	}
	
	/**
	 * 增加活动
	 * @param activity 活动实体类
	 * @return int 新增活动的id
	 * @date 2016-11-14 13:58
	 * */
	public int insert(Activity activity){
		String[] pros = activity.getSelectedPros().split(",");
		String[] rgns = activity.getSelectedRgn().split(",");
		String cAdmin = activity.getcAdmin();
		//渠道拼接，如果最后一个字符是逗号，则去掉
		String endStr = activity.getChannel().substring(activity.getChannel().length()-1);
		if(",".equals(endStr)){
			String temp = activity.getChannel().substring(0, activity.getChannel().length()-1);
			activity.setChannel(temp);
		}
		System.out.println(activity.getBackgroundImageLos()+"     ==================   ");
		 String sql = "insert into activity(name, company_id,act_time_start, act_time_end,is_limit_scan_region, "
		 		+ " act_base_nub,channel,"
		 		+ " c_time, c_admin, u_time, u_admin,cash_time_end,background_image_get,background_image_win,sta,is_auto_draw,background_image_los) values"
		 		+ " (:name, :companyId,:actTimeStart, :actTimeEnd,"
		 		+ " :isLimitScanRegion, :actBaseNub, :channel,:cTime, "
		 		+ ":cAdmin, :uTime, :uAdmin,:cashTimeEnd,:backgroundImageGet,:backgroundImageWin,:sta,:isAutoDraw,:backgroundImageLos)";
	        
	        Date currentDate = new Date();
	        activity.setcTime(currentDate.getTime());
	        activity.setuTime(currentDate.getTime());
	        activity.setSta(0);//活动状态默认0，0不可用，1可用
	        
	        KeyHolder keyHolder=new GeneratedKeyHolder(); 
	        //增加活动基本信息
	        SqlParameterSource ps=new BeanPropertySqlParameterSource(activity); 
	        namedParameterJdbcTemplate.update(sql, ps, keyHolder);
	        
	        int activityId = keyHolder.getKey().intValue();
	       
	        //增加活动与产品关联信息
	        ActivityProduct apro = new ActivityProduct();
	        for(int i = 0; i < pros.length; i++){
	        	apro.setActivityId(activityId);
	        	apro.setProductId(Integer.parseInt(pros[i]));
	        	apro.setcAdmin(cAdmin);
	        	this.insertActivityProduct(apro);
	        }
	        
	      //增加活动与区域关联
	        ActivityRegion r = new ActivityRegion();
	        for(int i = 0; i < rgns.length; i++){
	        	if(!("undefined").equals(rgns[i])){
	        		r.setActivityId(activityId);
		        	r.setRgnRegionId(Integer.parseInt(rgns[i]));
		        	this.insertActivityRegion(r);
	        	}
	        }
	        
	        return activityId;
	}
	/**
	 * 增加活动与区域关联关系
	 * @param ar 活动与区域关联关系 实体类
	 * @date 2016-11-21 14:01
	 * */
	private void insertActivityRegion(ActivityRegion ar){
		String sql = "insert into activity_region(activity_id, rgn_region_id"
		 		+ ") values (:activityId, :rgnId)";
	        HashMap<String, Object> paramMap = new HashMap<>();
	        paramMap.put("activityId", ar.getActivityId());
	        paramMap.put("rgnId", ar.getRgnRegionId());
	        
	        namedParameterJdbcTemplate.update(sql, paramMap);
	}
	
	/**
	 * 增加活动与产品关联关系
	 * @param aproduct 活动与产品关联关系 实体类
	 * @date 2016-11-14 14:01
	 * */
	private void insertActivityProduct(ActivityProduct aproduct){
		String sql = "insert into activity_product(activity_id, product_id,"
		 		+ " c_time, c_admin) values (:activityId, :productId,:cTime,:cAdmin)";
	        HashMap<String, Object> paramMap = new HashMap<>();
	        paramMap.put("activityId", aproduct.getActivityId());
	        paramMap.put("productId", aproduct.getProductId());
	        Date currentDate = new Date();
	        paramMap.put("cTime", currentDate.getTime());
	        paramMap.put("cAdmin", aproduct.getcAdmin());
	       /* try {
				templateTool.save(logistics);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}*/
	        
	        namedParameterJdbcTemplate.update(sql, paramMap);
	}
	
	/**
	 * 活动列表
	 * @param name 活动名称
	 * @param cAdmin 创建人
	 * @param sta 活动状态（1正常，0未启用，2手动停用）
	 * @param channel 渠道
	 * @param page 分页游标
	 * @return Pageable<Activity> 活动分页列表
	 * @date 2016-11-14 14:19
	 * */
	public Pageable<Activity> query(Integer companyId,String name, String cAdmin, Integer sta, String channel, int page) {
        Object[] args = {};
        StringBuffer sqlQueryResultCount = new StringBuffer("select count(1) from activity where 1 = 1 and company_id = ?");
        StringBuffer sqlQueryResult = new StringBuffer("select * from activity where 1 = 1  and company_id = ?");

        args = ArrayUtils.add(args, companyId);
        StringBuffer sqlWhere = new StringBuffer() ;
        
        if(StringUtils.isNotBlank(name)){
        	sqlWhere.append(" and name like ?");
        	args = ArrayUtils.add(args, "%" + name + "%");
        }
        if(StringUtils.isNotBlank(cAdmin)){
        	sqlWhere.append(" and cAdmin like ?");
        	args = ArrayUtils.add(args, "%" + cAdmin + "%");
        }
        if(StringUtils.isNotBlank(channel)){
        	sqlWhere.append(" and channel like ?");
        	args = ArrayUtils.add(args, "%" + channel + "%");
        }
        if(sta != null){
        	//if(sta !=0){
        		sqlWhere.append(" and sta = ?");
            	args = ArrayUtils.add(args, sta);
        	//}
        	
        }

        Integer resultTotal = 0;
        // 查询记录总数
        if(sqlWhere.toString() != ""){
        	resultTotal = jdbcTemplate.queryForObject(sqlQueryResultCount.append(sqlWhere).toString(), Integer.class, args);
        	// 查询记录
        	sqlQueryResult.append(sqlWhere).append("  order by c_time desc limit ?,?");
        }else{
        	resultTotal = jdbcTemplate.queryForObject(sqlQueryResultCount.toString(), Integer.class, args);
        	sqlQueryResult.append("  order by c_time desc limit ?,?");
        }
        
        args = ArrayUtils.add(args, (page - 1) * getPageSize());
        args = ArrayUtils.add(args, getPageSize());
        List<Activity> result = jdbcTemplate.query(sqlQueryResult.toString(), args, new BeanPropertyRowMapper<>(Activity.class));
        return new Pageable<>(page, getPageSize(), resultTotal, result);
    }
	/**
	 * 活动列表（数据导出用）
	 * @param companyId 公司id
	 * @param name 活动名称
	 * @param sta 活动状态（1正常，0未启用，2手动停用）
	 * @param channel 渠道
	 * @return List<Activity> 活动分页列表
	 * @date 2016-12-07 16:15
	 * */
	public List<Activity> queryForExport(Integer companyId,String name, Integer sta, String channel) {
		Object[] args = {};
		StringBuffer sqlQueryResult = new StringBuffer("select * from activity where 1 = 1  and company_id = ?");
		
		args = ArrayUtils.add(args, companyId);
		StringBuffer sqlWhere = new StringBuffer() ;
		
		if(StringUtils.isNotBlank(name)){
			sqlWhere.append(" and name like ?");
			args = ArrayUtils.add(args, "%" + name + "%");
		}
		
		if(StringUtils.isNotBlank(channel)){
			sqlWhere.append(" and channel like ?");
			args = ArrayUtils.add(args, "%" + channel + "%");
		}
		if(sta != null){
			sqlWhere.append(" and sta = ?");
			args = ArrayUtils.add(args, sta);
		}
		
		sqlQueryResult.append(sqlWhere).append("  order by c_time desc ");
		List<Activity> result = jdbcTemplate.query(sqlQueryResult.toString(), args, new BeanPropertyRowMapper<>(Activity.class));
		return result;
	}
	/**
	 * 启用、停用活动
	 * @param activityId 活动id
	 * @param type 操作类型:1启用,2停用
	 * @return int 受影响的记录行数
	 * @date 2016-11-20 10:35
	 * */
	public int usingActivity(String activityId,String type) {
		int num = Integer.parseInt(type);
	    if(num==1){//启用活动
			//活动状态：（1可用，0不可用）
			String sql="update activity set sta=1 where id= ?";
			jdbcTemplate.update(sql,activityId);
			
		}else if(num==2){//停用活动
			String sql="update activity set sta=2 where id= ?";
			jdbcTemplate.update(sql,activityId);
		}
        return 1;
	}
	/**
	 * 根据活动id获取区域
	 * @param activityId 活动id
	 * @return String 区域名称字符串
	 * @date 2016-11-22 13:30
	 * */
	public String getRegionByActivityId(String activityId){
		String sql="SELECT  rr.`Name`,rr.ParentId FROM rgn_region rr LEFT JOIN activity_region ar on rr.ID = ar.rgn_region_id WHERE ar.activity_id=?";
		List<RgnRegion> result = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(RgnRegion.class),activityId);
		String str = "";
		if(result.size() > 0){
			for(int i = 0; i < result.size(); i++){
				if(result.get(i).getParentId() == 1){//已经是省级了
					str += result.get(i).getName() + "，";
				}else{
					str += this.getRegionName(result.get(i).getParentId(),new StringBuffer()).append(result.get(i).getName()).toString() + "，";
				}
				
			}
		}
		
		if(StringUtils.isNotBlank(str)){
			str = str.substring(0, str.length()-1);
		}
		
		return str;
	}
	/**
	 * 根据活动id获取区域
	 * @param activityId 活动id
	 * @return List<ActivityRegion> 活动区域关联列表
	 * @date 2016-11-22 13:30
	 * */
	public List<ActivityRegion> getRegionById(String activityId){
		String sql="SELECT  rr.`Name`,rr.ParentId,rr.id,ar.id 'activityRegionId' FROM rgn_region rr LEFT JOIN activity_region ar on rr.ID = ar.rgn_region_id WHERE ar.activity_id=?";
		List<RgnRegion> result = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(RgnRegion.class),activityId);
		
		List<ActivityRegion> arList = new ArrayList<ActivityRegion>();
		
		if(result.size() > 0){
			for(int i = 0; i < result.size(); i++){
				ActivityRegion ar = new ActivityRegion();
				//区域id
				ar.setRgnRegionId(result.get(i).getId());
				//活动与区域关联id
				ar.setId(String.valueOf(result.get(i).getActivityRegionId()));
				//区域名称字符串
				if(result.get(i).getParentId() == 1){//已经是省级了
					ar.setSelectRgn(result.get(i).getName());
				}else{
					ar.setSelectRgn(this.getRegionName(result.get(i).getParentId(),new StringBuffer()).append(result.get(i).getName()).toString());
				}
				
				arList.add(ar);
			}
		}
		
		return arList;
	}
	/**
	 * 拼接区域
	 * @param parentId 父节点id
	 * @return StringBuffer 区域名称字符串
	 * @date 2016-11-24 19:53
	 * */
	private StringBuffer getRegionName(int parentId,StringBuffer buffer){
		
		String sql = "select * from rgn_region where id = ?";
		RgnRegion rgn = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<RgnRegion>(RgnRegion.class), parentId);
		//字符串往前追加
		if(buffer != null){
			buffer.insert(0, rgn.getName());
		}
		
		if(rgn.getParentId() !=1 && !rgn.getName().equals("中国")){
			getRegionName(rgn.getParentId(),buffer);//调用自身实现递归
		}
		
		return buffer;
	}
	
	/**
	 * 删除活动与区域关联
	 * @param activityId 活动id
	 * @return int 受影响的记录行数
	 * @date 2016-11-25 10:19
	 * */
	/*public int delRgnByActivityId(String activityId) {
        
		String sql = "DELETE from rgn_region WHERE id IN(SELECT ar.rgn_region_id from activity_region ar where ar.activity_id = ?);";
        return jdbcTemplate.update(sql,activityId);
	}*/
	/**
	 * 删除活动与区域关联
	 * @param activityRegionId 活动与区域关联id
	 * @return int 受影响的记录行数
	 * @date 2016-11-29 10:23
	 * */
	public int delRgnByActivityRegionId(String activityRegionId) {
		String sql = "DELETE FROM activity_region WHERE id =?";
		return jdbcTemplate.update(sql,activityRegionId);
	}
	
	/**
	 * 增加中奖规则
	 * @param aprize 中奖规则 实体类
	 * @date 2016-11-14 14:08
	 * */
	public int insertActivityPrize(ActivityPrize aprize){
		String sql = "insert into activity_prize(activity_id, prize_id,grade_name,same_winning_limit_times,max_winnng_times,"
				+ "nub,odds,"
		 		+ " is_attention,c_time, c_admin,is_input) values (:activityId, :prizeId,:gradeName, :sameWinningLimitTimes, :maxWinnngTimes,"
		 		+ ":nub, :odds,"
		 		+ ":isAttention,:cTime,:cAdmin,:is_input)";
	        HashMap<String, Object> paramMap = new HashMap<>();
	        paramMap.put("activityId", aprize.getActivityId());
	        paramMap.put("prizeId", aprize.getPrizeId());
	        paramMap.put("gradeName", aprize.getGradeName());
	        paramMap.put("sameWinningLimitTimes", aprize.getSameWinningLimitTimes());
	        paramMap.put("maxWinnngTimes", aprize.getMaxWinnngTimes());
	        paramMap.put("nub", aprize.getNub());
	        paramMap.put("odds", aprize.getOdds());//中奖比例=投放数量/参奖基数
			paramMap.put("isAttention", aprize.getIsAttention());
	        paramMap.put("cTime", (new Date()).getTime());
	        paramMap.put("cAdmin", aprize.getcAdmin());
	        paramMap.put("is_input", aprize.getIsInput());
	       
	        
	      return  namedParameterJdbcTemplate.update(sql, paramMap);
	}
	
	 /**
	  * 中奖规则列表
	  * @param page 分页游标
	  * @param activityId 活动id
	  * @return Pageable<ActivityPrize> 中奖规则分页列表
	  * @date 2016-11-23 11:54
	  * */
	 public Pageable<ActivityPrize> queryActivityPrize(int page,String activityId) {
		 Object[] args = {};
		 StringBuffer sqlWhere = new StringBuffer();
		 StringBuffer sqlQueryResultCount = new StringBuffer("select count(1) from activity_prize ap left join prize p on ap.prize_id=p.id where 1=1 ");
		 StringBuffer sqlQueryResult = new StringBuffer("select ap.*,p.name 'prizeName',p.type 'prizeType',p.price 'prizePrice' from activity_prize ap left join prize p "
				 + "on ap.prize_id=p.id where 1=1 ");
		 
		 
		 if(StringUtils.isNotBlank(activityId)){
			 sqlWhere.append(" and ap.activity_id = ?");
			 args = ArrayUtils.add(args, activityId);
		 }
		 // 查询记录总数
		 Integer resultTotal = jdbcTemplate.queryForObject(sqlQueryResultCount.append(sqlWhere).toString(), Integer.class,args);
		 
		 // 查询记录
		 sqlQueryResult.append(sqlWhere).append("  order by ap.c_time desc limit ?,?");
		 args = ArrayUtils.add(args, (page - 1) * getPageSize());
		 args = ArrayUtils.add(args, getPageSize());
		 List<ActivityPrize> result = jdbcTemplate.query(sqlQueryResult.toString(), args, new BeanPropertyRowMapper<>(ActivityPrize.class));
		 return new Pageable<>(page, getPageSize(), resultTotal, result);
	 }
	 
	 	/**
		 * 删除中奖规则
		 * @param activityPrizeId 中奖规则id
		 * @return int 受影响的记录数
		 * @date 2016-11-23 11:59
		 * */
		public int deleteActivityPrize(int activityPrizeId) {
	        String sql = "delete from activity_prize where id = ?";
	        return jdbcTemplate.update(sql, activityPrizeId);
	    }
		
		
		public Pageable<Product> querySelected(Integer companyId, String keyword, Integer state, int page) {
	        Object[] args = {};
	        StringBuffer sqlWhere = new StringBuffer();
	        StringBuffer sqlQueryResultCount = new StringBuffer("select count(1) from product prd left join brand brd on "
	        		+ "prd.brand_id = brd.id left join staff stf on prd.create_staff_id = stf.id left join pbc on "
	        		+ "prd.pbc_id = pbc.id"
	        		+ " where 1=1 and prd.company_id = ? ");
	        //<!--state2:1说明有未过期的记录，0 无未过期的记录-->
	        StringBuffer sqlQueryResult = new StringBuffer("select prd.*,brd.name 'brandName',stf.account 'createStaffAccount',"
	        		+ "pbc.pcount 'pbcPcount', "
	        		+ "CASE WHEN(SELECT COUNT(*) from activity_product WHERE product_id = prd.id) > 0 THEN 1 ELSE 0 end AS 'activityProductIdIsExists', "
	        		+ "CASE WHEN (SELECT count(*) from activity ac LEFT JOIN activity_product ap ON ac.id= ap.activity_id WHERE ac.sta =0 and ap.product_id = prd.id) > 0 THEN 1 ELSE 0 END AS 'state1' , "
	        		+ "CASE WHEN(SELECT count(*) from activity ac LEFT JOIN activity_product ap ON ac.id= ap.activity_id WHERE ac.sta =1 AND ac.act_time_end > UNIX_TIMESTAMP(NOW())*1000 and ap.product_id = prd.id) > 0 THEN 1 ELSE 0 END as 'state2', "
	        		+" CASE WHEN (SELECT count(*) from activity ac LEFT JOIN activity_product ap ON ac.id= ap.activity_id WHERE ac.sta =2 and ap.product_id = prd.id) > 0 THEN 1 ELSE 0 END AS 'state3' "
	        		+ "from product prd left join brand brd on prd.brand_id = brd.id left join staff stf on "
	        		+ "prd.create_staff_id = stf.id left join pbc on prd.pbc_id = pbc.id "
	        		+ "where 1=1 and prd.company_id = ? ");

	        args = ArrayUtils.add(args, companyId);
	        if(StringUtils.isNotBlank(keyword)){
	        	sqlWhere.append("  and (prd.name like ? or prd.number = ? or prd.barcode = ? or brd.name like ?)");
	            args = ArrayUtils.add(args, "%" + keyword + "%");
	            args = ArrayUtils.add(args, keyword);
	            args = ArrayUtils.add(args, keyword);
	            args = ArrayUtils.add(args, "%" + keyword + "%");
	        }
	        
	        if (state != null) {
	            sqlWhere.append(" and prd.state = ?");
	            args = ArrayUtils.add(args, state);
	        }

	        // 查询记录总数
	        Integer resultTotal = jdbcTemplate.queryForObject(sqlQueryResultCount.append(sqlWhere).toString(), Integer.class, args);
	        // 查询记录
	        sqlQueryResult.append(sqlWhere).append("  order by prd.create_time desc limit ?,?");
	        args = ArrayUtils.add(args, (page - 1) * getPageSize());
	        args = ArrayUtils.add(args, getPageSize());
	        List<Product> result = jdbcTemplate.query(sqlQueryResult.toString(), args, new BeanPropertyRowMapper<>(Product.class));
	        return new Pageable<>(page, getPageSize(), resultTotal, result);
	    }
		
		/**
		 * 根据中奖规则id获取中奖规则
		 * @param activityPrizeId 中奖规则id
		 * @return ActivityWinning 兑奖记录详情
		 * @date 2016-11-20 19:50
		 * */
	    public ActivityPrize getActivityPrizeByapId(String activityPrizeId) {
	    	String sql = "SELECT * from activity_prize where id=?";
	    	
	    	List<ActivityPrize> result = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(ActivityPrize.class),activityPrizeId);
	    	if(result.size() == 0){
	    		return new ActivityPrize();
	    	}else{
	    		return result.get(0);
	    	}
	    	
	    }
	    
	    /**
	     * 根据活动id获取中奖比例之和
	     * @param activityId 活动id
	     * @return double 中奖比例之和
	     * @date 2016-12-08 19:01
	     * */
	    public double getTotalOdds(String activityId){
	    	Integer resultTotal = 0;
	    	String sql = "select * from activity_prize where activity_id = ?";
			List<ActivityPrize> result = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(ActivityPrize.class),activityId);
			if(result.size() > 0){
				String sql1 = "SELECT sum(odds) from activity_prize WHERE activity_id = ? ";
				resultTotal = jdbcTemplate.queryForObject(sql1, Integer.class, activityId);
				return resultTotal;
			}else{
				return 0;
			}
	    }
	    /**
	     * 根据奖品等级名称和活动id 查看中奖规则是否存在
	     * @param activityId 活动id
	     * @param gradeName 奖品等级名称
	     * @return boolean true存在，false不存在
	     * @date 2016-12-12 10:10
	     * */
	    public boolean isExistsActivityPrize(String activityId, String gradeName){
	    	String sql = "select * from activity_prize where activity_id = ? and grade_name = ? ";
	    	List<ActivityPrize> result = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(ActivityPrize.class),activityId,gradeName);
	    	if(result.size() > 0){
	    		return true;
	    	}else{
	    		return false;
	    	}
	    }
	    /**
	     * 根据活动名称 查看活动是否存在
	     * @param companyId 公司名称
	     * @param name 活动名称
	     * @return boolean true存在，false不存在
	     * @date 2016-12-12 10:36
	     * */
	    public boolean isExistsActivity(String companyId, String name){
	    	String sql = "select * from activity where company_id = ? and name = ? ";
	    	List<Activity> result = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Activity.class),companyId,name);
	    	if(result.size() > 0){
	    		return true;
	    	}else{
	    		return false;
	    	}
	    }
	    
	    
}
